﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="r_elfutec_alteracao_de_conteudo.aspx.cs" Inherits="relatorios_r_elfutec_alteracao_de_conteudo" %>

<%
    string Permissao_RelatoriosElfutec = "" + this.Session["Permissao_RelatoriosElfutec"];
    if (Permissao_RelatoriosElfutec != "1")
    {
        this.Response.Redirect("../login.aspx");
    }        
%>
<div style="background-color: White;">

    <%
        bool hasResults = false;
        DateTime now = DateTime.Now;
        string month = this.Request["month"];
        string year = this.Request["year"];            
        int iMonth = -1;
        int iYear = -1;            
        int maxDay = 31;
            
        // Month
        if (string.IsNullOrEmpty(month) == true)
        {
            iMonth = now.Month;
        }
        else
        {
            try
            {
                iMonth = int.Parse(month);
            }
            catch { iMonth = now.Month; }
        }
        // Year            
        if (string.IsNullOrEmpty(year) == true)
        {
            iYear = now.Year;
        }
        else
        {
            try
            {
                iYear = int.Parse(year);
            }
            catch { iYear = now.Year; }                
        }

        maxDay = DateTime.DaysInMonth(iYear, iMonth);          
        //DateTime targetDate = DateTime.Now;     
        string message = "";
    %>
    <center>
        <form>
            <table>
                <tr>
                    <td>Mês</td>
                    <td>Ano</td>
                    <td></td>
                </tr>

                <tr>
                    <td>
                        <input type="text" id="month" name="month" value="<%= iMonth %>" />
                    </td>
                    <td><input type="text" id="year" name="year" value="<%= iYear %>" /></td>
                    <td><input type="submit" value="ok" /></td>
                </tr>
            </table>
        </form>
    </center>

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Redator', 'Alterações'],
                <%
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.MySql, PL.General.Instance.ElfutecConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
    
                    message = "";
                    try
                    {   
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                                                
                        string sql = "";
                        sql += " SELECT ";
                        sql += " COUNT(tb_redatores.Id_Redator) AS Total, tb_redatores.redator "; 
                        sql += " FROM "; 
                        sql += " tb_LogEditarTexto INNER JOIN tb_redatores ON (tb_redatores.Id_Redator=tb_LogEditarTexto.Id_Redator) "; 
                        sql += " WHERE ";
                        sql += " tb_LogEditarTexto.Data like '%/" + targetDate.Month + "/" + targetDate.Year + "'";                        
                        sql += " GROUP BY redator ";
                        //this.Response.Write(sql);
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {            
                            string solution = "" + reader["redator"];
                            string total = "" + reader["Total"];
                            if (string.IsNullOrEmpty(solution) == false && total != "0")
                            {
                                hasResults = true;
                                this.Response.Write("['" + solution + "', " + total.Replace(",", ".") + "],");
                            }
                        }
                        reader.Close();                        
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();                    
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Alteração de conteúdo no mês'
                    };
                    var chart = new google.visualization.LineChart(document.getElementById('r_mensalidade_elfutec_alteracao_conteudo'));
                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_mensalidade_elfutec_alteracao_conteudo" style="width: 80%; height: 200px;">
        <center>nenhum dado encontrado</center>
    </div>

    <br />

    <%
        string graphicData = "";
        hasResults = false;
        PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.MySql, PL.General.Instance.ElfutecConnectionString);
        PL.DataBase.DAO.Instance.Connector.Open();
        List<TripleString> triples = new List<TripleString>();        
        try
        {
            DateTime targetDate = new DateTime(iYear, iMonth, 1);
            DateTime maxDate = new DateTime(iYear, iMonth, maxDay);

            // NOMES
            List<string> names = new List<string>();

            string sql = "";
            sql += " SELECT tb_redatores.redator ";
            sql += " FROM  tb_LogEditarTexto INNER JOIN tb_redatores ON (tb_redatores.Id_Redator=tb_LogEditarTexto.Id_Redator) ";
            sql += " WHERE  ";
            sql += " tb_LogEditarTexto.Data LIKE '%/" + maxDate.Month + "/" + maxDate.Year + "' ";
            sql += " GROUP BY redator ";
            sql += " ORDER BY redator ";
            //this.Response.Write(sql);
            var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            while (reader.Read() == true)
            {
                names.Add("" + reader["redator"]);
            }
            reader.Close();

            sql = "";
            sql += " SELECT * FROM  ";

            sql += " 	(SELECT * FROM ";
            sql += " 		(	 ";
            sql += " 				(SELECT tb_redatores.Id_Redator, tb_redatores.redator AS redator1";
            sql += "                    FROM  tb_LogEditarTexto INNER JOIN tb_redatores ON (tb_redatores.Id_Redator=tb_LogEditarTexto.Id_Redator)  WHERE  tb_LogEditarTexto.Data  LIKE '%/" + maxDate.Month + "/" + maxDate.Year + "' GROUP BY redator ";
            sql += " 				) usuarios ";

            sql += " 			JOIN ";
            sql += " 				(SELECT * FROM  ";
            sql += " 					(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) Datas1 FROM ";
            sql += " 					(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, ";
            sql += " 					(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, ";
            sql += " 					(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, ";
            sql += " 					(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, ";
            sql += " 					(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v ";
            sql += " 					WHERE Datas1 BETWEEN '" + targetDate.Year + "-" + this.AdjustDate("" + targetDate.Month) + "-" + this.AdjustDate("" + targetDate.Day) + "' AND '" + maxDate.Year + "-" + this.AdjustDate("" + maxDate.Month) + "-" + this.AdjustDate("" + maxDate.Day) + "' ";
            sql += " 				) todasDatas ";
            sql += " 		) ";
            sql += " 	) bloco ";

            sql += " 	LEFT JOIN ";

            sql += " 	(SELECT  COUNT(tb_redatores.Id_Redator) AS Total, tb_redatores.Id_Redator, tb_redatores.redator, STR_TO_DATE(tb_LogEditarTexto.Data, '%d/%m/%Y') AS Datas2 ";
            sql += " 		FROM  tb_LogEditarTexto INNER JOIN tb_redatores ON (tb_redatores.Id_Redator=tb_LogEditarTexto.Id_Redator)  WHERE  tb_LogEditarTexto.Data LIKE '%/" + maxDate.Month + "/" + maxDate.Year + "' GROUP BY redator, STR_TO_DATE(tb_LogEditarTexto.Data, '%d/%m/%Y') ";
            sql += " 	) contagem ";

            sql += " 	ON (bloco.Datas1 = contagem.Datas2 AND bloco.Id_Redator = contagem.Id_Redator) ";
            //sql += " GROUP BY bloco.redator1 ";
            sql += " ORDER BY bloco.Datas1, bloco.redator1 ";
            //this.Response.Write(sql);
            //this.Response.Write("<br />");
            //this.Response.Write("<br />");
            reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            while (reader.Read() == true)
            {
                hasResults = true;

                string worker = "" + reader["redator1"];
                string total = "" + reader["Total"];
                string date = "" + reader["Datas1"];

                TripleString triple = new TripleString();
                triple.Value1 = worker;
                triple.Value2 = total.Replace(",", ".");                
                triple.Value3 = date;

                if (string.IsNullOrEmpty(triple.Value2) == true)
                {
                    triple.Value2 = "0";
                }

                triples.Add(triple);
            }
            reader.Close();

            // Names
            graphicData = "['Dia',";
            foreach (var theName in names)
            {
                graphicData += "'" + theName + "',";
            }
            graphicData = graphicData.Substring(0, graphicData.Length - 1);
            graphicData += "]";
            
            //graphicData += " = " + names.Count;                
            //graphicData += "<br />";
            
            // Values
            for (int i = 1; i <= maxDate.Day; ++i)
            {
                int theCount = 0;
                string currentDay = i + "/" + maxDate.Month + "/" + maxDate.Year;
                graphicData += ", ['" + currentDay + "'";
                foreach (var theName in names)
                {
                    foreach (var theTriple in triples)
                    {
                        if (theTriple.Value1 == theName && theTriple.Value3 == maxDate.Year + "-" + this.AdjustDate("" + maxDate.Month) + "-" + this.AdjustDate("" + i))
                        {
                            graphicData += ", " + theTriple.Value2;
                            theCount++;
                        }                        
                    }
                }
                graphicData += "]";
                //graphicData += " = " + theCount;
                //graphicData += "<br />";
            }
        }
        catch (Exception e)
        {
            message = e.Message;
        }

        PL.DataBase.DAO.Instance.Connector.Close();       
        //this.Response.Write(graphicData);       
    %>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([                
                <%
                %>
                <%= graphicData %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Alteração de conteúdo por dia'
                    };
                    var chart = new google.visualization.LineChart(document.getElementById('r_mensalidade_elfutec_alteracao_conteudo_dia'));
                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_mensalidade_elfutec_alteracao_conteudo_dia" style="width: 80%; height: 200px;">
        <center>nenhum dado encontrado</center>
    </div>
</div>